package com.asiainfo.kbisheet.util


import java.io.ByteArrayOutputStream;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import sun.misc.BASE64Encoder
import jxl.Cell
import jxl.Sheet
import jxl.Workbook
import jxl.biff.formula.StringOperator
import jxl.format.Colour
import jxl.format.UnderlineStyle
import jxl.write.Label
import jxl.write.Number
import jxl.write.NumberFormat
import jxl.write.WritableCellFormat
import jxl.write.WritableFont
import jxl.write.WritableSheet
import jxl.write.WritableWorkbook

class ExcelUtil {
	protected static final Log log = LogFactory.getLog(ExcelUtil.class)
	String createExcel(excelXmlString){
		log.info "创建Excel..."
		def excelXml = new XmlSlurper().parseText(excelXmlString)	
		def headerXml = excelXml.header
		def rowsXML = excelXml.rows		
		int allHeight = Integer.parseInt(headerXml.@height.text())
		ByteArrayOutputStream os = new ByteArrayOutputStream()
		WritableWorkbook wwb = Workbook.createWorkbook(os)
		WritableSheet ws = wwb.createSheet(excelXml.@title.toString(), 0)
		
		//创建Excel表头
		addExcelHeader(ws,headerXml.column,allHeight,0,0)
		
		fillExcelRows(ws,headerXml,rowsXML,allHeight)
		   
		// 写入Exel工作表
		wwb.write()
		  // 关闭Excel工作薄对象
		wwb.close()
		
		(new BASE64Encoder()).encode(os.toByteArray())
	}

	def addExcelHeader(ws,columnsXml,allHeight,x,y){
		columnsXml.each{column->
			String title = column.@title.text()		
			int width = Integer.parseInt(column.@width.text())
			int height =  Integer.parseInt(column.@height.text())
			addExcelHeader(ws,title,x,y,x + width -1,y + height -1)
			

			if(column.column.size() > 0){
				addExcelHeader(ws,column.column, allHeight, x,y + height)
			}
			x = x + width
		}	
	}

	def addExcelHeader(ws,title,x1,y1,x2,y2){
		WritableCellFormat cellFormat=new WritableCellFormat()
	    cellFormat.setAlignment(jxl.format.Alignment.CENTRE)
	    cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE)
	    cellFormat.setWrap(true)

		Label label = new Label(x1,y1,title)
	    label.setCellFormat(cellFormat)
		ws.addCell(label)
		if(x1 < x2 || y1 < y2){
			ws.mergeCells(x1,y1,x2,y2)
		}
	}

	def fillExcelRows(ws,headerXml,rowsXML,allHeight){
		def flatColumnXml = headerXml.'**'.grep{it.@name != null && it.@name.text().length() > 0}	
//		System.out.println(flatColumnXml.size() + ";" + rowsXML.row.size())
		int y = allHeight
		WritableCellFormat nfPercent = new WritableCellFormat(new NumberFormat("0.00%"))
		WritableCellFormat nfInt = new WritableCellFormat(new NumberFormat("#,##0"))
		WritableCellFormat nfCurrency = new WritableCellFormat(new NumberFormat("#,##0.00"))
		rowsXML.row.each {row->
			flatColumnXml.eachWithIndex {column,index->
				String format = column.@format
				String value = row."${column.@name}".text()
				if(value !=null && value.length() > 0){
					def cell
					if(format !=null && format.length() > 0){
						if(format.equalsIgnoreCase("percent")){
							cell = new Number(index,y, new Double(value), nfPercent)
						}
						else if(format.equalsIgnoreCase("int")){
							cell = new Number(index,y, new Double(value), nfInt)
						}
						else {
							cell = new Number(index,y, new Double(value), nfCurrency)
						}				    
					}
					else{
						cell = new Label(index,y,value)				
					}
					ws.addCell(cell);
				}
				
			}
			y++
		}
	}
}
